"""
@desc: orm基类(仅限单表查询，多表需要自定义)
@author: forward
@time: 2021/11/25 17:09
"""

from logging import getLogger
from typing import Any, ClassVar, Generic, List, Optional, Type, TypeVar

from pydantic import BaseModel
from sqlalchemy import (
    BinaryExpression,
    ClauseElement,
    ColumnExpressionArgument,
    UnaryExpression,
    delete,
    func,
    insert,
    or_,
    select,
    update,
)

from forward.dependencies.database import db as databases
from forward.utils.page_utils import PageSchema, PageUtils

logger = getLogger("base_orm")
T = TypeVar("T", bound=BaseModel)


class BaseORM(Generic[T]):
    """
    T:输出pydantic模型

    """

    MODEL: ClassVar[Type[Any]]
    OUT_SCHEMA: Type[T]

    def __init__(self, db: None = None):
        self.db = db or databases

    async def create(self, data: dict) -> Optional[T]:
        """
        BaseORM create method
        """
        values_ = {}
        for k, v in data.items():
            if hasattr(self.MODEL, k):
                values_[k] = v
        statement = insert(self.MODEL).values(**values_)
        record_id = await self.db.execute(statement)
        print("更新记录", record_id, data)
        return await self.get_one_by_id(record_id)

    async def bulk_create(self, data_lists: List[dict]) -> int | None:
        """
        BaseORM 批量增加
        """
        creates = []
        for item in data_lists:
            data = {}
            for key, value in item.items():
                if not hasattr(self.MODEL, key):
                    continue  # 跳过不存在的字段
                data[key] = value
            creates.append(data)
        statement = insert(self.MODEL)
        return await self.db.execute_many(statement, creates)

    async def get_one_by_id(self, id: int) -> Optional[T]:
        """
        BaseORM 根据id获取一条记录
        """

        record = await self.db.fetch_one(select(self.MODEL).where(self.MODEL.id == id))
        if not record:
            return None
        return self.OUT_SCHEMA.model_validate(record, from_attributes=True)

    async def get_one_by_filters(
        self, where: ColumnExpressionArgument[bool] | None = None, **kwargs
    ) -> Optional[T]:
        """
        BaseORM 根据过滤条件获取一条记录
        默认检测顺序是id 从小到大，
        返回第一个
        """
        where_clauses = []
        for k, v in kwargs.items():
            if hasattr(self.MODEL, k):
                where_clauses.append(getattr(self.MODEL, k) == v)
        if where is not None:
            where_clauses.append(where)
        statement = select(self.MODEL).where(*where_clauses).order_by(self.MODEL.id)
        record = await self.db.fetch_one(statement)
        if not record:
            return None
        return self.OUT_SCHEMA.model_validate(record, from_attributes=True)

    async def get_all_by_filters(
        self, where: ColumnExpressionArgument[bool] | None = None, **kwargs
    ) -> List[T]:
        """
        BaseORM 根据过滤条件获取所有记录
        """
        where_clauses = []
        for key, value in kwargs.items():
            if hasattr(self.MODEL, key):
                where_clauses.append(getattr(self.MODEL, key) == value)
        query = select(self.MODEL).where(*where_clauses)
        if where is not None:
            query = query.where(where)
        records = await self.db.fetch_all(query)
        return [
            self.OUT_SCHEMA.model_validate(r, from_attributes=True) for r in records
        ]

    async def get_count_by_filters(
        self, where: ColumnExpressionArgument[bool] | None = None, **kwargs
    ) -> int:
        """
        BaseORM 根据过滤条件查询记录总数
        """
        where_clauses = []

        for key, value in kwargs.items():
            if hasattr(self.MODEL, key):
                where_clauses.append(getattr(self.MODEL, key) == value)

        if where is not None:
            where_clauses.append(where)
        subq = select(self.MODEL.id).where(*where_clauses).subquery()
        stmt = select(func.count()).select_from(subq)
        total = await self.db.fetch_val(stmt)
        return total or 0

    async def update(self, id: int, data: dict) -> Optional[T]:
        """
        BaseORM 修改数据
        """
        update_data = {}
        for key, value in data.items():
            if hasattr(self.MODEL, key):
                update_data[key] = value
        statement = update(self.MODEL).where(self.MODEL.id == id).values(**update_data)
        await self.db.execute(statement)
        print("update", id, data)
        return await self.get_one_by_id(id)

    async def bulk_update(self, ids: List[int], data: dict) -> int:
        """
        BaseORM 批量修改数据
        """
        update_data = {}
        for key, value in data.items():
            if hasattr(self.MODEL, key):
                update_data[key] = value
        statement = (
            update(self.MODEL).where(self.MODEL.id.in_(ids)).values(**update_data)
        )
        return await self.db.execute(statement)

    async def delete_by_id(self, id: int) -> bool:
        """
        BaseORM 根据ID删除数据
        """
        statement = delete(self.MODEL).where(self.MODEL.id == id)
        result = await self.db.execute(statement)
        return result > 0

    async def delete_by_filters(
        self, where: ColumnExpressionArgument[bool] | None = None, **kwargs
    ) -> bool:
        where_clauses = []
        for key, value in kwargs.items():
            if hasattr(self.MODEL, key):
                where_clauses.append(getattr(self.MODEL, key) == value)
        if where is not None:
            where_clauses.append(where)
        statement = delete(self.MODEL).where(*where_clauses)
        result = await self.db.execute(statement)
        return result > 0

    async def soft_delete_by_id(self, id: int) -> bool:
        """
        BaseORM 根据ID软删除数据
        """
        statement = update(self.MODEL).where(self.MODEL.id == id).values(is_delete=1)
        result = await self.db.execute(statement)
        return result > 0

    async def bulk_delete_by_ids(self, ids: List[int]) -> int:
        """
        BaseORM 根据多ID批量删除数据
        """
        statement = delete(self.MODEL).where(self.MODEL.id.in_(ids))
        return await self.db.execute(statement)

    async def bulk_soft_delete_by_ids(self, ids: List[int]) -> int:
        statement = update(self.MODEL).where(self.MODEL.id.in_(ids)).values(is_delete=1)
        return await self.db.execute(statement)

    async def exists_by_id(self, id: int) -> bool:
        """
        BaseORM 根据ID判断数据是否存在
        """
        record = await self.db.fetch_one(
            select(self.MODEL.id).where(self.MODEL.id == id)
        )
        return record is not None

    async def paginate(
        self,
        order_by: List[Any],
        where: List[UnaryExpression] | None = None,
        page_num: int = 1,
        page_size: int = 15,
        **kwargs,
    ) -> PageSchema[T]:
        """
        BaseORM 分页查询
        """
        where_clauses = []

        for key, value in kwargs.items():
            if hasattr(self.MODEL, key):
                where_clauses.append(getattr(self.MODEL, key) == value)
        if where:
            where_clauses.extend(where)
        query = select(self.MODEL).where(*where_clauses).order_by(*order_by)
        return await PageUtils.paginate(
            db=self.db,
            query=query,
            schema=self.OUT_SCHEMA,
            page_num=page_num,
            page_size=page_size,
        )

    async def get_list(self) -> List[T]:
        """
        BaseORM 获取所有记录的列表
        """
        records = await self.db.fetch_all(select(self.MODEL))
        return [
            self.OUT_SCHEMA.model_validate(r, from_attributes=True) for r in records
        ]

    async def search(
        self, fields: List[str], keyword: str, exact_match: bool = False
    ) -> List[T]:
        """
        BaseORM 支持对多个字段进行搜索（模糊或精确匹配）

        :param fields: 要搜索的字段名列表
        :param keyword: 搜索关键词
        :param exact_match: 是否精确匹配
        :return: 查询结果模型列表
        """

        where_clauses = []
        for field in fields:
            if hasattr(self.MODEL, field):
                column = getattr(self.MODEL, field)
                if exact_match:
                    where_clauses.append(column == keyword)
                else:
                    where_clauses.append(column.ilike(f"%{keyword}%"))

        records = await self.db.fetch_all(select(self.MODEL).where(or_(*where_clauses)))
        return [
            self.OUT_SCHEMA.model_validate(r, from_attributes=True) for r in records
        ]

    async def exists_by_filters(
        self, where: list[BinaryExpression] | None = None, **kwargs
    ) -> bool:
        """
        根据关键词判断是否存在
        """
        where_clauses = []
        for key, value in kwargs.items():
            if hasattr(self.MODEL, key):
                where_clauses.append(getattr(self.MODEL, key) == value)
        if where:
            where_clauses.extend(where)
        logger.debug(f"{self.MODEL.__name__} exists_by_filters: {where_clauses}")
        record = await self.db.fetch_one(select(self.MODEL.id).where(*where_clauses))
        return record is not None
